/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package repol190.ocorrencia;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;
import java.util.Vector;
import java.util.logging.Level;
import java.util.logging.Logger;
import repol190.util.ConexaoBD;

/**
 *
 * @author micael
 */
public class RepositorioEnderecoDataBase implements RepositorioEndereco{
    
     private Connection con;

    @Override
    public void inserirEndereco(Endereco novoEndereco) {
       con = new ConexaoBD().abrirConexao();
       
        String sql = "insert into endereco (id_endereco, rua, bairro, "
                + "complemento, x, y) values (?, ?, ?, ?, ?, ?)";
        
        try {
            PreparedStatement comando = con.prepareStatement(sql);
            
            comando.setInt(1, novoEndereco.getId());
            comando.setString(2, novoEndereco.getRua());
            comando.setString(3, novoEndereco.getBairro());
            comando.setString(4, novoEndereco.getComplemento());
            comando.setInt(5, novoEndereco.getPosicao().getX());
            comando.setInt(6, novoEndereco.getPosicao().getY());
            
            comando.execute();
            
            comando.close();
            con.close();
        } catch (SQLException ex) {
            Logger.getLogger(RepositorioEnderecoDataBase.class.getName()).log(Level.SEVERE, null, ex);
        }
            
    }

    @Override
    public void excluirEndereco(int id) {
       con = new ConexaoBD().abrirConexao();
       
       String sql = "delete from endereco where id_endereco=?";
       
        try {
            PreparedStatement comando = con.prepareStatement(sql);
            
            comando.setInt(1, id);
            comando.execute();
            
            comando.close();
            con.close();
            
        } catch (SQLException ex) {
            Logger.getLogger(RepositorioEnderecoDataBase.class.getName()).log(Level.SEVERE, null, ex);
        }
       
    }

    @Override
    public Endereco buscaEndereco(int id) {
        con = new ConexaoBD().abrirConexao();
        
        Endereco endereco = null;
        Posicao posicao = null;
        ResultSet rs = null;

        String sql = "select * from endereco where id_endereco="+id;
        try {
            PreparedStatement comando = con.prepareStatement(sql);
            
            rs = comando.executeQuery();
            
            if(rs.next()){
                
                posicao = new Posicao(rs.getInt("x"), rs.getInt("y"));
                endereco = new Endereco(rs.getInt("id_endereco"),rs.getString("bairro"), 
                        rs.getString("rua"), rs.getString("complemento"), posicao);
                
            }
            
            comando.close();
            rs.close();
            con.close();
        } catch (SQLException ex) {
            Logger.getLogger(RepositorioEnderecoDataBase.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        
        return endereco;
    }

    @Override
    public void atualizarEndereco(Endereco endereco) {
        con = new ConexaoBD().abrirConexao();
        
        String sql = "update endereco set rua=? bairro=?"
                + "complemento=? x=? y=? where id_endereco=?";
        try {
            PreparedStatement comando = con.prepareStatement(sql);
            
            comando.setString(1, endereco.getRua());
            comando.setString(2, endereco.getBairro());
            comando.setString(3, endereco.getComplemento());
            comando.setInt(4, endereco.getPosicao().getX());
            comando.setInt(4, endereco.getPosicao().getY());
            
            comando.execute();
            
            comando.close();
            con.close();
        } catch (SQLException ex) {
            Logger.getLogger(RepositorioEnderecoDataBase.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    @Override
    public Collection<Endereco> listarEnderecos() {
        con = new ConexaoBD().abrirConexao();
        
        Vector<Endereco> resp = new Vector<Endereco>();
        Endereco endereco = null;
        Posicao posicao = null;
        ResultSet rs = null;

        String sql = "select * from endereco";
        try {
            PreparedStatement comando = con.prepareStatement(sql);
            
            rs = comando.executeQuery();
            
            while(rs.next()){
                posicao = new Posicao(rs.getInt("x"), rs.getInt("y"));
                endereco = new Endereco(rs.getInt("id_endereco"),rs.getString("bairro"), 
                        rs.getString("rua"), rs.getString("complemento"), posicao);
                
                resp.add(endereco);
                
            }
            comando.close();
            rs.close();
            con.close();
                    
        } catch (SQLException ex) {
            Logger.getLogger(RepositorioEnderecoDataBase.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        
        return resp;
    }
    
}
